#
# Copyright 2008 Spencer Davis <SpencerDavis91@gmail.com>
#
# This file is part of Tuxedo.
#
# Tuxedo is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# Tuxedo is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with Tuxedo; if not, see <http://gnu.org/licenses/.>
#

import os
import sys
import gtk
import task
from task import Task
from sqlite3 import dbapi2 as sqlite

# Set constant for the databse location
DBPATH = os.path.join(os.path.expanduser('~'), '.tuxedo')

class Database:
    """Manages database interaction"""
    def __init__(self):
        """Creates the connection to the database"""
        # Create a cursor for the datbase
        cursor = sqlite.connect(DBPATH).cursor()

        # Try to execute a query to make sure that the database exists
        try:
            cursor.execute('SELECT * FROM tasks')

        # If there was a problem with the database, create the table
        except sqlite.OperationalError:
            # Try to create the table
            try:
                cursor.execute('CREATE TABLE tasks(name VARCHAR(50),\
                                               priority int,\
                                               status int,\
                                               id INTEGER PRIMARY KEY NOT NULL,\
                                               year int,\
                                               month int,\
                                               day int)')

            # If there was an error, display an error dialog
            except sqlite.Error, message:
                errordialog = gtk.MessageDialog(None, 0, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in verifying database:\n\n' + message.message)
                errordialog.run()
                errordialog.destroy()

        self.tasksdict = {}

    def readTask(self, id):
        """Reads the task with the given id from the database and returns it"""
        # Create a cursor for the database
        cursor = sqlite.connect(DBPATH).cursor()

        # Put the id into a tuple for the query
        data = id,

        # Try to execute the query
        try:
            cursor.execute('SELECT * FROM tasks WHERE id=?;', data)

        # If there was an error, display an error dialog 
        except sqlite.Error, message:
            errordialog = gtk.MessageDialog(None, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in reading a task from database:\n\n' + message.message)
            errordialog.run()
            errordialog.destroy()

        # Fetch one result and store it
        result = cursor.fetchone()

        # Create a Task object to store the information in
        t = Task()

        # Set the values from the database results
        t.name = result[0]
        t.priority = result[1]
        t.status = result[2]
        t.id = result[3]
        t.duedate = (result[4], result[5], result[6])

        # Return the task object
        return t

    def readTasks(self):
        """Reads all of the tasks from the database and returns them in a list"""
        # Create a cursor for the database
        cursor = sqlite.connect(DBPATH).cursor()

        # Create a tuple with the constant for completed tasks for the query
        data = task.TASK_COMPLETED,

        # Try to get all of the tasks that are not completed
        try:
            cursor.execute('SELECT * from tasks\
                            WHERE status != ?\
                            ORDER BY status DESC,\
                            year ASC,\
                            month ASC,\
                            day ASC,\
                            priority ASC,\
                            id ASC;', data);

        # If the query failed, display an error dialog
        except sqlite.Error, message:
            errordialog = gtk.MessageDialog(None,  0, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in reading tasks from database:\n\n' + message.message)
            errordialog.run()
            errordialog.destroy()

        # Fetch all of the results
        rows = cursor.fetchall()

        # Create an empty list to store all of the tasks in
        tasks = []

        # For every row of data returned from the database
        for row in rows:
            # Create a Task object to store the data in
            t = Task()

            # Save the data into the Task
            t.name = row[0]
            t.priority = row[1]
            t.status = row[2]
            t.id = row[3]
            t.duedate = (row[4], row[5], row[6])

            # Append the Task to the list
            self.tasksdict[t.id] = t
            tasks.append(t)

        # Get all of the tasks that are completed
        try:
           cursor.execute('SELECT * from tasks\
                           WHERE status = ?\
                           ORDER BY year ASC,\
                           month ASC,\
                           day ASC,\
                           priority ASC,\
                           id ASC;', data)

        # If the query failed, display an error dialog
        except sqlite.Error, message:
            errordialog = gtk.MessageDialog(None, 0, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in reading tasks from database:\n\n' + message.message)
            errordialog.run()
            errordialog.destroy()

        # Fetch all of the results
        rows = cursor.fetchall()

        # For every row of data returned from the database
        for row in rows:

            # Create a Task object to store the data in
            t = Task()

            # Save the data into the Task
            t.name = row[0]
            t.priority = row[1]
            t.status = row[2]
            t.id = row[3]
            t.duedate = (row[4], row[5], row[6])

            # Append the Task to the list
            self.tasksdict[t.id] = t
            tasks.append(t)

        # Return the list of tasks
        return tasks

    def saveTask(self, t):
        """Takes a Task object and saves it to the database"""
        # Connect to the database
        conn = sqlite.connect(DBPATH)

        # If the task has an id, update the existing database entry
        if t.id is not None:
            # Store the data for the query in a tuple
            data = (t.name, t.priority, t.status, t.duedate[0], t.duedate[1],
                    t.duedate[2], t.id)

            # Try to execute the query
            try:
                conn.execute('UPDATE tasks SET name=?,\
                                               priority=?,\
                                               status=?,\
                                               year=?,\
                                               month=?,\
                                               day=?\
                                               WHERE id=?;', data)

            # If the query failed, display an error dialog
            except sqlite.Error, message:
                errordialog = gtk.MessageDialog(None, 0, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in writing task to database:\n\n' + message.message)
                errordialog.run()
                errordialog.destroy()

        # If the task does not have an id, create a new entry in the database
        else:
            # Store the data for the query in a tuple
            data = (t.name, t.priority, t.status,
                    t.duedate[0], t.duedate[1], t.duedate[2])

            # Try to execute the query
            try:
                conn.execute('INSERT INTO tasks\
                              (name, priority, status, year, month, day)\
                              VALUES(?, ?, ?, ?, ?, ?);', data)

            # If the query failed, display an error dialog
            except sqlite.Error, message:
                errordialog = gtk.MessageDialog(None, 0, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in writing task to database:\n\n' + message.message)
                errordialog.run()
                errordialog.destroy()

        # Commit the changes to the database
        conn.commit()

    def deleteTask(self, t):
        """Takes a Task object and deletes it from the database"""
        # Store the id of the task in a tuple for the query
        data = t.id,

        # Connect to the database
        conn = sqlite.connect(DBPATH)

        # Try to execute the query
        try:
            conn.execute('DELETE FROM tasks WHERE id=?;', data)

        # If the query failed, display an error dialog
        except sqlite.Error, message:
            errordialog = gtk.MessageDialog(None, 0, gtk.MESSAGE_ERROR, gtk.BUTTONS_OK, 'Error in removing task from database:\n\n' + message.message)
            errordialog.run()
            errordialog.destroy()

        # Commit the changes to the database
        conn.commit()
